<?php


namespace app\back\model;

use PhpOffice\PhpSpreadsheet\Exception as ExcelException;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use think\facade\Db;
use think\response\Json;
use \Exception;

class DataModel
{
    private static $_orderTable = 'order'; // 订单表
    private static $_adminTable = 'admin'; // 管理员表
    private static $_companyTable = 'company'; // 公司表
    private static $_goodsTable = 'goods'; // 商品表
    private static $_goodsUnitTable = 'goods_unit'; // 商品单位
    private static $_goodsLogTable = 'goods_log'; // 商品操作日志

    /**获取数据统计
     * @param $input
     * @param $info
     * @return Json
     */
    public static function getTotalList($input, $info)
    {
        try {
            $map = [
                ['o.create_time', 'between', [strtotime($input['start']), strtotime($input['end'])]]
            ];
            $table = Db::table(self::$_orderTable)->alias('o');
            switch ($info['admin_level']) {
                case 0: // 超管
                    if ($input['agent_name'] !== '') $map[] = ['ag.name', 'like', "%{$input['agent_name']}%"];
                    if ($input['operator_name'] !== '') $map[] = ['op.name', 'like', "%{$input['operator_name']}%"];
                    if ($input['company_name'] !== '') $map[] = ['c.name', 'like', "%{$input['company_name']}%"];
                    $field = 'ag.name as agent_name, op.name as operator_name, c.name as company_name,count(*) as order_total,sum(if(o.status in (-3,-5,4,5,6),total_price - back_money,0)) as total_price, sum(if(o.status in (4,5,6),1,0)) as ok_order_num,sum(if(o.status = -4,1,0)) as cancel_order_num';
                    $table->leftJoin([self::$_adminTable => 'op'], 'op.id = o.admin_id')
                        ->leftJoin([self::$_adminTable => 'ag'], 'ag.id = op.pid')
                        ->leftJoin([self::$_companyTable => 'c'], 'c.id = o.group_id');
                    break;
                case 1: // 代理商
                    $map[] = ['op.pid', '=', $info['id']];
                    if ($input['operator_name'] !== '') $map[] = ['op.name', 'like', "%{$input['operator_name']}%"];
                    if ($input['company_name'] !== '') $map[] = ['c.name', 'like', "%{$input['company_name']}%"];
                    $field = 'op.name as operator_name, c.name as company_name,count(*) as order_total,sum(if(o.status in (-3,-5,4,5,6),total_price - back_money,0)) as total_price, sum(if(o.status in (4,5,6),1,0)) as ok_order_num,sum(if(o.status = -4,1,0)) as cancel_order_num';
                    $table->leftJoin([self::$_adminTable => 'op'], 'op.id = o.admin_id')
                        ->leftJoin([self::$_companyTable => 'c'], 'c.id = o.group_id');
                    break;
                case 2: // 运营商
                    $map[] = ['o.admin_id', '=', $info['id']];
                    if ($input['company_name'] !== '') $map[] = ['c.name', 'like', "%{$input['company_name']}%"];
                    $field = 'c.name as company_name,count(*) as order_total,sum(if(o.status in (-3,-5,4,5,6),total_price - back_money,0)) as total_price, sum(if(o.status in (4,5,6),1,0)) as ok_order_num,sum(if(o.status = -4,1,0)) as cancel_order_num';
                    $table->leftJoin([self::$_companyTable => 'c'], 'c.id = o.group_id');
                    break;
            }
            $table = $table->where($map)->field($field)->group('o.group_id');
            $count = $table->count();
            if ($count == 0) return jsonSuccess('暂无数据', ['count' => 0, 'list' => []]);
            $list = $table->page($input['page'], $input['limit'])->order('o.group_id', 'desc')->select()->toArray();
            return jsonSuccess('获取成功', ['count' => $count, 'list' => $list]);
        } catch (Exception $e) {
            return jsonFail('服务内部错误~');
        }
    }

    /**获取商品销售统计
     * @param $input
     * @param $info
     * @return Json
     */
    public static function getGoodsTotalList($input, $info)
    {
        try {
            $map = [
                ['gl.admin_id', '=', $info['id']],
                ['gl.type', '=', 2]
            ];
            if ($input['name'] !== '') $map[] = ['g.name', 'like', "%{$input['name']}%"];
            if ($input['start'] && $input['end']) $map[] = ['gl.create_time', 'between', [strtotime($input['start']), strtotime($input['end'])]];
            $table = Db::table(self::$_goodsUnitTable)->alias('gu')
                ->join([self::$_goodsTable => 'g'], 'gu.id = g.unit_id')
                ->join([self::$_goodsLogTable => 'gl'], 'gl.goods_id = g.id')
                ->where($map)->group('gl.goods_id');
            $count = $table->count();
            if ($count == 0) return jsonSuccess('暂无数据', ['count' => 0, 'list' => []]);
            $list = $table->field('gl.goods_id as id,g.name,sum(weight) as total_num,gu.name as unit_name')->page($input['page'], $input['limit'])->order(['total_num' => 'desc', 'id' => 'desc'])->select()->toArray();
            return jsonSuccess('获取成功', ['count' => $count, 'list' => $list]);
        } catch (Exception $e) {
            return jsonFail('服务内部错误~');
        }
    }

    /**获取商品销售统计excel
     * @param $input
     * @param $info
     * @return Json
     */
    public static function getGoodsTotalExcel($input, $info)
    {
        try {
            $map = [
                ['gl.admin_id', '=', $info['id']],
                ['gl.type', '=', 2]
            ];
            if ($input['name'] !== '') $map[] = ['g.name', 'like', "%{$input['name']}%"];
            if ($input['start'] && $input['end']) $map[] = ['gl.create_time', 'between', [strtotime($input['start']), strtotime($input['end'])]];
            $data = Db::table(self::$_goodsUnitTable)->alias('gu')
                ->join([self::$_goodsTable => 'g'], 'gu.id = g.unit_id')
                ->join([self::$_goodsLogTable => 'gl'], 'gl.goods_id = g.id')
                ->field('gl.goods_id as id,g.name,sum(weight) as total_num,gu.name as unit_name')
                ->where($map)
                ->group('gl.goods_id')
                ->order(['total_num' => 'desc', 'id' => 'desc'])
                ->select()->toArray();
            $spreadsheet = new Spreadsheet();
            $sheet = $spreadsheet->getActiveSheet();
            //设置工作表标题名称
            $sheet->setTitle('sheet1');
            $sheet->setCellValue('A1', '商品ID');
            $sheet->setCellValue('B1', '商品名称');
            $sheet->setCellValue('C1', '销量');
            $sheet->setCellValue('D1', '单位');
            $sheet->fromArray(
                $data,
                null,
                'A2'
            );

//            $spreadsheet->setActiveSheetIndex(0);
            $writer = new Xlsx($spreadsheet);
            ob_start(); //打开缓冲区
            $writer->save('php://output');
            $xlsData = ob_get_contents();
            ob_end_clean(); // 清除缓冲
            return jsonSuccess('excl生成成功，进行下载...', "data:application/vnd.ms-excel;base64," . base64_encode($xlsData));
        } catch (ExcelException $e) {
            return jsonFail($e->getMessage());
        } catch (Exception $e) {
            return jsonFail('服务内部错误~');
        }
    }
}